﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Maticsoft.DBUtility;

namespace ElingOA6_Admin_DSJCJ.DAL
{
    /// <summary>
    /// 人员岗位DAL
    /// </summary>
    public class HREmplPosition
    {
        /// <summary>
        /// 获取人员数据
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="deptNo">部门编号</param>
        /// <param name="entityNo">组织机构代码</param>
        /// <returns>人员数据DS</returns>
        public DataSet GetDeptEmplList(string deptNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.FPositionNo, a.FDeptNo, a.FEntityNo, a.FEmplNo, b.FEmpName, b.FSeq from t_HREmplPosition a, t_HREmpl b ");
            strSql.Append("where a.FEmplNo = b.FEmplNo and a.FDeptNo=@FDeptNo and a.FEntityNo=@FEntityNo order by FSeq asc");
            SqlParameter[] parameters = {
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获取所有人员数据
        /// <creator>wangl</creator>
        /// <date>2013-07-29</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <returns>人员数据DataSet</returns>
        public DataSet GetMainDeptEmplALL(string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.FPositionNo, a.FDeptNo, a.FEntityNo, a.FEmplNo, b.FEmpName, b.FSeq from t_HREmplPosition a, t_HREmpl b ");
            strSql.Append("where a.FEmplNo = b.FEmplNo AND a.FEntityNo=@FEntityNo AND FIsMainPosition='Y' order by FSeq asc");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 获取所有人员数据（人员去向不包含虚拟人员）
        /// <creator>zhongyl</creator>
        /// <date>2013-07-29</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <returns>人员数据DataSet</returns>
        public DataSet GetLeaveMainDeptEmplALL(string entityNo)
        {
            
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.FPositionNo, a.FDeptNo, a.FEntityNo, a.FEmplNo, b.FEmpName, b.FSeq from t_HREmplPosition a, t_HREmpl b ");
            strSql.Append("where a.FEmplNo = b.FEmplNo AND a.FEntityNo=@FEntityNo AND FIsMainPosition='Y' and (b.FEmpStatus='01' or b.FEmpStatus='02') and FIsJoin='Y' order by FSeq asc");
            SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 获取所有人员数据（参与网上考勤不包含虚拟人员）
        /// <creator>wangl</creator>
        /// <date>2014-03-14</date>
        /// </summary>
        /// <param name="entityNo">公司编号</param>
        /// <returns>人员数据DataSet</returns>
        public DataSet GetAttendanceMainDeptEmplALL(string entityNo,string deptNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.FPositionNo, a.FDeptNo, a.FEntityNo, a.FEmplNo, b.FEmpName, b.FSeq,b.FDeptName,b.FDeptSeq");
            //int days=DateTime.DaysInMonth(year, month);
            //string dayCol = ",";
            //for (int i = 1; i < days + 1; i++)
            //{
            //    dayCol += "'' as day" + i + ",";
            //}
            //dayCol = dayCol.Substring(0, dayCol.LastIndexOf(","));
            //strSql.Append(dayCol);
            strSql.Append(" from t_HREmplPosition a, v_HREmpl b ");
            strSql.Append("where a.FEmplNo = b.FEmplNo AND a.FEntityNo=@FEntityNo AND a.FIsMainPosition='Y' and b.FIsMainPosition='Y' and (b.FEmpStatus='01' or b.FEmpStatus='02')");
            strSql.Append(" and b.FIsAttendance='Y' ");

            if (deptNo != "")
            {
                strSql.Append(" and a.FDeptNo=@FDeptNo ");

                strSql.Append(" order by b.FDeptSeq,b.FSeq asc");
                SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50),
                                            new SqlParameter("@FDeptNo", SqlDbType.NVarChar,12)
                                            };
                parameters[0].Value = entityNo;
                parameters[1].Value = deptNo;
                return DbHelperSQL.Query(strSql.ToString(), parameters);
            }
            else
            {
                strSql.Append(" order by b.FDeptSeq,b.FSeq asc");
                SqlParameter[] parameters = {
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
                parameters[0].Value = entityNo;
                return DbHelperSQL.Query(strSql.ToString(), parameters);
            }
        }
       

        /// <summary>
        /// 获取员工岗位信息
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="emplNo">人员编号</param>
        /// <param name="entityNo">组织机构编码</param>
        /// <returns>人员岗位信息DS</returns>
        public DataSet GetEmplPositionInfo(string emplNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.FEntityNo, a.FDeptNo, a.FPositionNo, a.FIsMainPosition, (select FPositionName from t_HRPosition where FPositionNo = a.FPositionNo and FDeptNo = a.FDeptNo and FEntityNo = a.FEntityNo) as FPositionName,  ");
            strSql.Append("(dbo.fn_getDepNames(a.FDeptNo , a.FEntityNo)) as FDeptName from t_HREmplPosition a  ");
            strSql.Append("where a.FEmplNo = @FEmplNo and a.FEntityNo=@FEntityNo");
            SqlParameter[] parameters = {
					new SqlParameter("@FEmplNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = emplNo;
            parameters[1].Value = entityNo;
            return DbHelperSQL.Query(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 判断部门是否存在人员
        /// <creator>zhongyl</creator>
        /// <date>2013-07-12</date>
        /// </summary>
        /// <param name="deptNo">部门编号</param>
        /// <param name="entityNo">组织机构编号</param>
        /// <returns>是否存在人员</returns>
        public bool ExistsDept(string deptNo, string entityNo)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_HREmplPosition");
            strSql.Append(" where FDeptNo=@FDeptNo and FEntityNo=@FEntityNo ");

            SqlParameter[] parameters = {
					new SqlParameter("@FDeptNo", SqlDbType.NVarChar,50),
					new SqlParameter("@FEntityNo", SqlDbType.NVarChar,50)};
            parameters[0].Value = deptNo;
            parameters[1].Value = entityNo;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);

        }

    }
}
